from flaskapp.util.Sqlalchemy import db
from sqlalchemy import func, case, or_, and_  # 导入 case 和其他必要的函数
from flaskapp.entity.User import User

class Message(db.Model):
    __tablename__ = 'message'

    message_id = db.Column(db.Integer, primary_key=True)
    sender_id = db.Column(db.Integer, db.ForeignKey('user.user_id'), nullable=False)
    receiver_id = db.Column(db.Integer, db.ForeignKey('user.user_id'), nullable=False)
    content = db.Column(db.Text, nullable=False)
    sent_at = db.Column(db.DateTime, default=db.func.current_timestamp())
    is_read = db.Column(db.Boolean, default=False)

    def __init__(self, sender_id, receiver_id, content):
        self.sender_id = sender_id
        self.receiver_id = receiver_id
        self.content = content

    def to_dict(self):
        return {
            'message_id': self.message_id,
            'sender_id': self.sender_id,
            'receiver_id': self.receiver_id,
            'content': self.content,
            'sent_at': self.sent_at.isoformat() if self.sent_at else None,
            'is_read': self.is_read
        }
class MessageQuery:
    @staticmethod
    def get_conversations(current_user_id):
        """获取用户的所有对话记录（不区分角色、不用 house_id）"""
        messages = db.session.query(Message).filter(
            or_(
                Message.sender_id == current_user_id,
                Message.receiver_id == current_user_id
            )
        ).order_by(desc(Message.sent_at)).all()

        seen = set()
        result = []

        for msg in messages:
            partner_id = msg.receiver_id if msg.sender_id == current_user_id else msg.sender_id
            if partner_id in seen:
                continue
            seen.add(partner_id)

            partner = db.session.query(User).filter_by(id=partner_id).first()
            partner_name = partner.username if partner else '未知用户'

            unread_count = db.session.query(func.count()).filter(
                Message.sender_id == partner_id,
                Message.receiver_id == current_user_id,
                Message.is_read == False
            ).scalar()

            result.append({
                'partner_id': partner_id,
                'partner_name': partner_name,
                'last_message': msg.content,
                'last_time': msg.sent_at.isoformat() if msg.sent_at else None,
                'unread_count': unread_count or 0
            })

        return result